﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Configuration;
using System.Data.SqlClient;

namespace SWEN_HMS.Classes
{
    public class userDB
    {

        public static string login(int staffID, string password)
        {
            string access = null;
            SqlConnection conn = null;
            EncodingAndDecodingcs en = new EncodingAndDecodingcs();
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["HMSConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * from Staff WHERE staffID=@staffID AND password=@pw";
                comm.Parameters.AddWithValue("@staffID", staffID);
                comm.Parameters.AddWithValue("@pw",en.Encode(password));

                SqlDataReader dr = comm.ExecuteReader();

                if (dr.Read())
                {
                    access = dr["accessLevel"].ToString();
                }

            }
            catch (SqlException e)
            {
                throw e;
            }
            return access;
        }
        public static staff getDetailByStaffID(int staffID)
        {
            staff s = new staff();
            EncodingAndDecodingcs en = new EncodingAndDecodingcs();
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["HMSConnectionString"].ConnectionString;
                conn.Open();

                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * from Staff WHERE staffID=@staffID";
                comm.Parameters.AddWithValue("@staffID", staffID);

                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    s = new staff();
                    s.staffId=Convert.ToInt32(dr["staffID"]);
                    s.password= (string)dr["password"];
                    s.staffName = (string)dr["staffName"];
                    s.dob = Convert.ToDateTime(dr["dob"]).ToShortDateString();
                    s.bankType = (string)dr["bankType"];
                    s.accountNo = Convert.ToInt32(dr["accountNo"]);
                    s.address = (string)dr["address"];
                    s.email = (string)dr["email"];
                    s.contactNo = Convert.ToInt32(dr["contactNo"]);
                    s.position = (string)dr["position"];
                    s.accessLevel = (string)dr["accessLevel"];
                }

                dr.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return s;
        }
        public static staff getPasswordByStaffID(int staffID)
        {
            staff s = new staff();
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["HMSConnectionString"].ConnectionString;
                conn.Open();

                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT password from Staff WHERE staffID=@staffID";
                comm.Parameters.AddWithValue("@staffID", staffID);
                

                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    s = new staff();
                 //   s.staffId = Convert.ToInt32(dr["staffID"]);
                    s.password = (string)dr["password"];
             
                }

                dr.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return s;
        }
        public static staff getDetailByStaffName(string staffName)
        {
            staff s = new staff();
             SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["HMSConnectionString"].ConnectionString;
                conn.Open();

                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * from Staff WHERE staffName=@staffName";
                comm.Parameters.AddWithValue("@staffName", staffName);

                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    s = new staff();
                    s.staffId = Convert.ToInt32(dr["staffID"]);
                    s.password = (string)dr["password"];
                    s.staffName = (string)dr["staffName"];
                    s.dob = Convert.ToDateTime(dr["dob"]).ToShortDateString();
                    s.bankType = (string)dr["bankType"];
                    s.accountNo = Convert.ToInt32(dr["accountNo"]);
                    s.address = (string)dr["address"];
                    s.email = (string)dr["email"];
                    s.contactNo = Convert.ToInt32(dr["contactNo"]);
                    s.position = (string)dr["position"];
                    s.accessLevel = (string)dr["accessLevel"];
                }

                dr.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return s;


        }
        public static int registerStaff(staff s)
        {
            int rowadded = 0;
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["HMSConnectionString"].ConnectionString;
                conn.Open();

                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "INSERT INTO Staff(password,staffName,dob,bankType,accountNo,address,email,contactNo,position,accessLevel)VALUES" +
                    "(@password,@staffName,@dob,@banktype,@accountno,@address,@email,@contactno,@position,@accessLevel)";

                comm.Parameters.AddWithValue("@password", s.password);
                comm.Parameters.AddWithValue("@staffName", s.staffName);
                comm.Parameters.AddWithValue("@dob", s.dob);
                comm.Parameters.AddWithValue("@banktype", s.bankType);
                comm.Parameters.AddWithValue("@accountno", s.accountNo);
                comm.Parameters.AddWithValue("@address", s.address);
                comm.Parameters.AddWithValue("@email", s.email);
                comm.Parameters.AddWithValue("@contactno", s.contactNo);
                comm.Parameters.AddWithValue("@position", s.position);
                comm.Parameters.AddWithValue("@accessLevel", s.accessLevel);

                rowadded = comm.ExecuteNonQuery();




            }
            catch (SqlException e)
            {
                throw e;
            }
            return rowadded;
        }
        public static int updateStaffInfo(staff s)
        {
            int rowupdated = 0;
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["HMSConnectionString"].ConnectionString;
                conn.Open();

                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "Update Staff SET staffName=@staffName, dob=@dob, bankType=@banktype, accountNo=@accountNo, address=@address, email=@email, contactNo=@contactno, position=@position, accessLevel=@accesslevel WHERE staffID=@staffID";
                comm.Parameters.AddWithValue("@staffName", s.staffName);
                comm.Parameters.AddWithValue("@dob", Convert.ToDateTime(s.dob));
                comm.Parameters.AddWithValue("@banktype",s.bankType);
                comm.Parameters.AddWithValue("@accountNo",s.accountNo);
                comm.Parameters.AddWithValue("@address",s.address);
                comm.Parameters.AddWithValue("@email",s.email);
                comm.Parameters.AddWithValue("@contactno", s.contactNo);
                comm.Parameters.AddWithValue("@position",s.position);
                comm.Parameters.AddWithValue("@accesslevel",s.accessLevel);
                comm.Parameters.AddWithValue("@staffID",s.staffId);

                rowupdated = comm.ExecuteNonQuery();
                conn.Close();
            }
            catch(SqlException e)
            {
                throw e;
            }
            return rowupdated;
          

        }
        public static int updateProfile(staff s)
        {
            int rowupdated = 0;
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["HMSConnectionString"].ConnectionString;
                conn.Open();

                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "Update Staff SET staffName=@staffName, dob=@dob, bankType=@banktype, accountNo=@accountNo, address=@address, email=@email, contactNo=@contactno WHERE staffID=@staffID";
                comm.Parameters.AddWithValue("@staffName", s.staffName);
                comm.Parameters.AddWithValue("@dob", Convert.ToDateTime(s.dob));
                comm.Parameters.AddWithValue("@banktype", s.bankType);
                comm.Parameters.AddWithValue("@accountNo", s.accountNo);
                comm.Parameters.AddWithValue("@address", s.address);
                comm.Parameters.AddWithValue("@email", s.email);
                comm.Parameters.AddWithValue("@contactno", s.contactNo);
                comm.Parameters.AddWithValue("@staffID", s.staffId);

                rowupdated = comm.ExecuteNonQuery();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return rowupdated;
          
        }
        public static int updatePW(staff s,string pw,string pass)
        {
            int rowupdated = 0;
            SqlConnection conn = null;
            try
            {
                 conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["HMSConnectionString"].ConnectionString;
                conn.Open();

                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "Update Staff Set password=@password Where password=@pass";
                comm.Parameters.AddWithValue("@password", pw);
                comm.Parameters.AddWithValue("@pass", pass);

                rowupdated = comm.ExecuteNonQuery();
            }
             catch(SqlException e)
            {
                 throw e;
             }
                return rowupdated;
           


        }
        public string getAccess(int staffID)
        {
                SqlConnection conn = null;
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["HMSConnectionString"].ConnectionString;
                conn.Open();

                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT accessLevel from Staff WHERE staffID=@staffID";
                comm.Parameters.AddWithValue("@staffID", staffID);

                SqlDataReader dr = comm.ExecuteReader();
                if (dr.Read())
                {
                    return dr["accessLevel"].ToString();
                }
                else
                {
                    return null;
                    dr.Close();
                    conn.Close();
                }

            }


        }
    }


